package acme.bloodtime.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import modelo.IArmaDO;
import modelo.IArmaduraDO;
import modelo.IArticuloDAO;
import modelo.IArticuloDO;
import modelo.IPocionDO;
import dao.api.DataObject;
import dao.api.FactoryDAO;
import dao.api.InterfaceDAO;

/**
 * 
 * @author Manuel A. Quintero V.
 * 
 */

public class ArticuloDAO extends BloodTimeBaseDAO implements IArticuloDAO {

    public ArticuloDAO() {
        // Empty
    }

    @Override
    public void createTable() throws SQLException {
        StringBuffer strbuf;

        // ----------------------------------------

        dropTable();

        // ----------------------------------------

        strbuf = new StringBuffer();

        strbuf.append("CREATE TABLE ");
        strbuf.append(getTableName());
        strbuf.append(" (");
        strbuf.append(ArticuloDO.ID);
        strbuf.append(" SERIAL PRIMARY KEY,    ");
        strbuf.append(ArticuloDO.NOMBRE);
        strbuf.append(" VARCHAR(15) NOT NULL,    ");
        strbuf.append(ArticuloDO.DESCRIPCION);
        strbuf.append(" VARCHAR(500) NOT NULL,    ");
        strbuf.append(ArticuloDO.IMAGEN);
        strbuf.append(" BYTEA,    ");
        strbuf.append(ArticuloDO.PRECIO_COMPRA);
        strbuf.append(" NUMERIC,    ");
        strbuf.append(ArticuloDO.PRECIO_VENTA);
        strbuf.append(" NUMERIC    ");
        strbuf.append(")");

        System.err.println(strbuf.toString());

        connection.createStatement().execute(strbuf.toString());
        try {
            InterfaceDAO armaDAO = FactoryDAO.getDAO(ArmaDAO.class, connectionBean);
            armaDAO.createTable();
            InterfaceDAO armaduraDAO = FactoryDAO.getDAO(ArmaduraDAO.class, connectionBean);
            armaduraDAO.createTable();
            InterfaceDAO pocionDAO = FactoryDAO.getDAO(PocionDAO.class, connectionBean);
            pocionDAO.createTable();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void insert(DataObject dataObject) throws SQLException {
        try {
            if (dataObject instanceof ArmaDO) {
                InterfaceDAO armaDAO = FactoryDAO.getDAO(ArmaDAO.class, connectionBean);
                armaDAO.insert(dataObject);
            } else if (dataObject instanceof ArmaduraDO) {
                InterfaceDAO armaduraDAO = FactoryDAO.getDAO(ArmaduraDAO.class, connectionBean);
                armaduraDAO.insert(dataObject);
            } else if (dataObject instanceof PocionDO) {
                InterfaceDAO pocionDAO = FactoryDAO.getDAO(PocionDAO.class, connectionBean);
                pocionDAO.insert(dataObject);
            } else {
                throw new IllegalStateException("!instanceof IArmaDO or ArmaduraDO or PocionDO");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    protected int internalInsertArticulo(DataObject dataObject, Class<? extends DataObject> dataObjectClass)
            throws SQLException {
        checkCache(dataObject, CHECK_INSERT);
        checkClass(dataObject, dataObjectClass, CHECK_INSERT);

        ArticuloDAO articuloDAO = new ArticuloDAO();

        ArticuloDO articuloDO = (ArticuloDO) dataObject;

        articuloDO.setId(getNextId());
        StringBuffer strbuf = new StringBuffer();

        strbuf.append("INSERT INTO ");
        strbuf.append(articuloDAO.getTableName());
        strbuf.append(" VALUES (");
        strbuf.append(articuloDO.getId());
        strbuf.append(", ");
        strbuf.append(singleQuotes(articuloDO.getNombre()));
        strbuf.append(", ");
        strbuf.append(singleQuotes(articuloDO.getDescripcion()));
        strbuf.append(", ");
        strbuf.append("?, ");
        strbuf.append(articuloDO.getPrecioCompra());
        strbuf.append(", ");
        strbuf.append(articuloDO.getPrecioVenta());
        strbuf.append(")");

        // ---------------------------------------------------------------
        // The PreparedStatement is used to do the insertion of the image
        // ---------------------------------------------------------------

        PreparedStatement ps = connection.prepareStatement(strbuf.toString());
        ps.setBytes(1, articuloDO.getImagen());

        ps.executeUpdate();
        ps.close();

        System.err.println(strbuf.toString());

        dtaSession.add(dataObject);

        return articuloDO.getId();

    }

    @Override
    public void update(DataObject dataObject) throws SQLException {
        try {
            if (dataObject instanceof IArmaDO) {
                InterfaceDAO armaDAO = FactoryDAO.getDAO(ArmaDAO.class, connectionBean);
                armaDAO.update(dataObject);
            } else if (dataObject instanceof IArmaduraDO) {
                InterfaceDAO armaduraDAO = FactoryDAO.getDAO(ArmaduraDAO.class, connectionBean);
                armaduraDAO.update(dataObject);
            } else if (dataObject instanceof IPocionDO) {
                InterfaceDAO pocionDAO = FactoryDAO.getDAO(PocionDAO.class, connectionBean);
                pocionDAO.update(dataObject);
            } else {
                throw new IllegalStateException("!instanceof IArmaDO or ArmaduraDO or PocionDO");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    protected void internalUpdateArticulo(DataObject dataObject, Class<? extends DataObject> dataObjectClass)
            throws SQLException {
        checkCache(dataObject, CHECK_UPDATE);
        checkClass(dataObject, dataObjectClass, CHECK_UPDATE);

        ArticuloDAO articuloDAO = new ArticuloDAO();

        ArticuloDO articuloDO = (ArticuloDO) dataObject;

        StringBuffer strbuf = new StringBuffer();

        strbuf.append("UPDATE ");
        strbuf.append(articuloDAO.getTableName());
        strbuf.append(" SET ");

        strbuf.append(ArticuloDO.NOMBRE);
        strbuf.append(" = ");
        strbuf.append(singleQuotes(articuloDO.getNombre()));

        strbuf.append(", ");

        strbuf.append(ArticuloDO.DESCRIPCION);
        strbuf.append(" = ");
        strbuf.append(singleQuotes(articuloDO.getDescripcion()));

        strbuf.append(", ");

        strbuf.append(ArticuloDO.IMAGEN);
        strbuf.append(" = ");
        strbuf.append(" ?, ");

        strbuf.append(ArticuloDO.PRECIO_COMPRA);
        strbuf.append(" = ");
        strbuf.append(articuloDO.getPrecioCompra());

        strbuf.append(", ");

        strbuf.append(ArticuloDO.PRECIO_VENTA);
        strbuf.append(" = ");
        strbuf.append(articuloDO.getPrecioVenta());

        strbuf.append(" WHERE ");
        strbuf.append(ArticuloDO.ID);
        strbuf.append(" = ");
        strbuf.append(articuloDO.getId());

        // ---------------------------------------------------------------
        // The PreparedStatement is used to do the insertion of the image
        // ---------------------------------------------------------------

        PreparedStatement ps = connection.prepareStatement(strbuf.toString());
        ps.setBytes(1, articuloDO.getImagen());

        ps.executeUpdate();
        ps.close();

        System.err.println(strbuf.toString());

    }

    @Override
    public void delete(DataObject dataObject) throws SQLException {
        try {
            if (dataObject instanceof IArmaDO) {
                InterfaceDAO armaDAO = FactoryDAO.getDAO(ArmaDAO.class, connectionBean);
                armaDAO.delete(dataObject);
            } else if (dataObject instanceof IArmaduraDO) {
                InterfaceDAO armaduraDAO = FactoryDAO.getDAO(ArmaduraDAO.class, connectionBean);
                armaduraDAO.delete(dataObject);
            } else if (dataObject instanceof IPocionDO) {
                InterfaceDAO pocionDAO = FactoryDAO.getDAO(PocionDAO.class, connectionBean);
                pocionDAO.delete(dataObject);
            } else {
                throw new IllegalStateException("!instanceof ArmaDO or ArmaduraDO or PocionDO");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    protected void internalDelete(DataObject dataObject, Class<? extends DataObject> dataObjectClass)
            throws SQLException {
        checkCache(dataObject, CHECK_DELETE);
        checkClass(dataObject, dataObjectClass, CHECK_DELETE);

        ArticuloDAO articuloDAO = new ArticuloDAO();

        StringBuffer strbuf = new StringBuffer();

        strbuf.append("DELETE FROM ");
        strbuf.append(articuloDAO.getTableName());

        strbuf.append(" WHERE ");
        strbuf.append(DataObject.ID);
        strbuf.append(" = ");
        strbuf.append(dataObject.getId());

        System.err.println(strbuf.toString());

        connection.createStatement().execute(strbuf.toString());

        dtaSession.del(dataObject);

    }

    public DataObject loadById(int id) throws SQLException {
        StringBuffer strbuf = new StringBuffer();

        ArticuloDAO articuloDAO = new ArticuloDAO();
        ArmaDAO armaDAO = new ArmaDAO();
        ArmaduraDAO armaduraDAO = new ArmaduraDAO();
        PocionDAO pocionDAO = new PocionDAO();

        strbuf.append("SELECT articulo.*, 1 as tipo FROM ");
        strbuf.append(getTableName() + "," + armaDAO.getTableName());
        strbuf.append(" WHERE  " + ArticuloDO.ID + "=" + id);
        strbuf.append(" AND " + articuloDAO.getTableName() + ArmaDO.ID + " = " + ArticuloDO.ID);
        strbuf.append(" UNION ");

        strbuf.append("SELECT articulo.*, 2 as tipo FROM ");
        strbuf.append(getTableName() + "," + armaduraDAO.getTableName());
        strbuf.append(" WHERE  " + ArticuloDO.ID + "=" + id);
        strbuf.append(" AND " + articuloDAO.getTableName() + ArmaduraDO.ID + " = " + ArticuloDO.ID);
        strbuf.append(" UNION ");

        strbuf.append("SELECT articulo.*, 3 as tipo FROM ");
        strbuf.append(getTableName() + "," + pocionDAO.getTableName());
        strbuf.append(" WHERE  " + ArticuloDO.ID + "=" + id);
        strbuf.append(" AND " + articuloDAO.getTableName() + PocionDO.ID + " = " + ArticuloDO.ID);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        if (rs.next()) {
            return resultSetToDO(rs);
        }

        return null;
    }

    @Override
    public List<DataObject> listAll(int lim, int off) throws SQLException {
        StringBuffer strbuf = new StringBuffer();

        ArmaDAO armaDAO = new ArmaDAO();
        ArmaduraDAO armaduraDAO = new ArmaduraDAO();
        PocionDAO pocionDAO = new PocionDAO();

        strbuf.append("SELECT articulo.*, 1 as tipo FROM ");
        strbuf.append(getTableName() + "," + armaDAO.getTableName());
        strbuf.append(" WHERE  " + ArticuloDO.ID + "=" + getTableName() + ArticuloDO.ID);
        strbuf.append(" UNION ");

        strbuf.append("SELECT articulo.*, 2 as tipo FROM ");
        strbuf.append(getTableName() + "," + armaduraDAO.getTableName());
        strbuf.append(" WHERE  " + ArticuloDO.ID + "=" + getTableName() + ArticuloDO.ID);
        strbuf.append(" UNION ");

        strbuf.append("SELECT articulo.*, 3 as tipo FROM ");
        strbuf.append(getTableName() + "," + pocionDAO.getTableName());
        strbuf.append(" WHERE  " + ArticuloDO.ID + "=" + getTableName() + ArticuloDO.ID);

        if (lim >= 0 && off >= 0) {
            strbuf.append(" LIMIT  ");
            strbuf.append(lim);
            strbuf.append(" OFFSET ");
            strbuf.append(off);
        }

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        List<DataObject> ret = new ArrayList<DataObject>();

        while (rs.next()) {
            ret.add(resultSetToDO(rs));
        }
        return ret;
    }

    @Override
    protected DataObject resultSetToDO(ResultSet rs) throws SQLException {
        StringBuffer strbuf = new StringBuffer();
        ArticuloDAO articuloDAO = new ArticuloDAO();
        ArmaDAO armaDAO = new ArmaDAO();
        ArmaduraDAO armaduraDAO = new ArmaduraDAO();
        PocionDAO pocionDAO = new PocionDAO();
        DataObject ret;

        switch (rs.getInt("tipo")) {
        case 1:

            ret = //
            (ArmaDO) dtaSession.getDtaByKey( //
                    ArmaDO.class, rs.getInt(ArmaDO.ID));

            if (ret != null) {
                return ret;
            }
            ret = new ArmaDO();
            articuloDAO.resulSetArticuloDO(ret, rs);

            strbuf.append("SELECT * FROM ");
            strbuf.append(armaDAO.getTableName());
            strbuf.append(" WHERE  " + getTableName() + ArticuloDO.ID + "=" + ret.getId());
            System.err.println(strbuf.toString());
            rs = //
            connection.createStatement().executeQuery(strbuf.toString());

            if (!rs.next()) {
                throw new IllegalStateException("!rs.next");
            }

            ((ArmaDO) ret).setHabArmasRequerida(rs.getInt(ArmaDO.HAB_ARMAS_REQUERIDA));
            ((ArmaDO) ret).setCantMejoraFuerza(rs.getInt(ArmaDO.CANT_MEJORA_FUERZA));
            return (ArmaDO) dtaSession.add(ret);
        case 2:

            ret = //
            (ArmaduraDO) dtaSession.getDtaByKey( //
                    IArmaduraDO.class, rs.getInt(IArmaduraDO.ID));

            if (ret != null) {
                return ret;
            }
            ret = new ArmaduraDO();
            articuloDAO.resulSetArticuloDO(ret, rs);

            strbuf.append("SELECT * FROM ");
            strbuf.append(armaduraDAO.getTableName());
            strbuf.append(" WHERE  " + getTableName() + ArticuloDO.ID + "=" + ret.getId());
            System.err.println(strbuf.toString());
            rs = //
            connection.createStatement().executeQuery(strbuf.toString());
            if (!rs.next()) {
                throw new IllegalStateException("!rs.next");
            }
            ((ArmaduraDO) ret).setHabArmasRequerida(rs.getInt(ArmaduraDO.HAB_ARMAS_REQUERIDA));
            ((ArmaduraDO) ret).setCantMejoraDefensa(rs.getInt(ArmaduraDO.CANT_MEJORA_DEFENSA));
            return (IArmaduraDO) dtaSession.add(ret);

        case 3:
            ret = //
            (PocionDO) dtaSession.getDtaByKey( //
                    PocionDO.class, rs.getInt(PocionDO.ID));

            if (ret != null) {
                return ret;
            }
            ret = new PocionDO();
            articuloDAO.resulSetArticuloDO(ret, rs);

            strbuf.append("SELECT * FROM ");
            strbuf.append(pocionDAO.getTableName());
            strbuf.append(" WHERE  " + getTableName() + ArticuloDO.ID + "=" + ret.getId());
            System.err.println(strbuf.toString());
            rs = //
            connection.createStatement().executeQuery(strbuf.toString());
            if (!rs.next()) {
                throw new IllegalStateException("!rs.next()");
            }
            ((PocionDO) ret).setAtributoObjetivo(rs.getInt(PocionDO.ATRIBUTO_OBJETIVO));
            ((PocionDO) ret).setCantMejoraAtributoObj(rs.getInt(PocionDO.CANT_MEJORA_ATRIBUTO_OBJ));
            return (PocionDO) dtaSession.add(ret);
        default:
            throw new RuntimeException("!istanceof IArmaDO or IArmaduraDO or PocionDO");

        }

    }

    protected void resulSetArticuloDO(DataObject ret, ResultSet rs) throws SQLException {

        ret.setId/*									*/(rs.getInt(ArticuloDO.ID));
        ((ArticuloDO) ret).setNombre/*				*/(rs.getString(ArticuloDO.NOMBRE));
        ((ArticuloDO) ret).setDescripcion/*			*/(rs.getString(ArticuloDO.DESCRIPCION));
        ((ArticuloDO) ret).setImagen/*				*/(rs.getBytes(ArticuloDO.IMAGEN));
        ((ArticuloDO) ret).setPrecioCompra/*		*/(rs.getFloat(ArticuloDO.PRECIO_COMPRA));
        ((ArticuloDO) ret).setPrecioVenta/*			*/(rs.getFloat(ArticuloDO.PRECIO_VENTA));

    }

    @Override
    protected int getNextId() throws SQLException {

        ArticuloDAO articuloDAO = new ArticuloDAO();

        StringBuffer strbuf = new StringBuffer();

        strbuf.append("SELECT nextval(");
        strbuf.append(singleQuotes(articuloDAO.getTableName() + "_id_seq"));
        strbuf.append(")");

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        if (!rs.next()) {
            throw new IllegalStateException("!rs.next()");
        }

        return rs.getInt("nextval");
    }

    public void loadVampiroArticuloList(IArticuloDO articuloDO) throws Exception {
        checkCache(articuloDO, CHECK_UPDATE);

        VampiroArticuloDAO vampiroArticuloDAO = (VampiroArticuloDAO) FactoryDAO.getDAO( //
                VampiroArticuloDAO.class, connectionBean);

        articuloDO.setVampiroArticuloList(vampiroArticuloDAO.listByArticuloId(articuloDO.getId()));
    }

    public void loadCiudadArticuloList(IArticuloDO articuloDO) throws Exception {
        checkCache(articuloDO, CHECK_UPDATE);

        CiudadArticuloDAO ciudadArticuloDAO = (CiudadArticuloDAO) FactoryDAO.getDAO( //
                CiudadArticuloDAO.class, connectionBean);

        articuloDO.setCiudadArticuloList(ciudadArticuloDAO.listByArticuloId(articuloDO.getId()));
    }

    @Override
    public List<IArticuloDO> listByCiudadId(int id) throws Exception {
        StringBuffer strbuf = new StringBuffer();

        CiudadArticuloDAO ciudadArticuloDAO = new CiudadArticuloDAO();
        ArticuloDAO articuloDAO = new ArticuloDAO();

        strbuf.append("SELECT " + getTableName() + ".* FROM ");
        strbuf.append(getTableName());
        strbuf.append(" JOIN " + ciudadArticuloDAO.getTableName());
        strbuf.append(" ON " + articuloDAO.getTableName() + "." + ArticuloDO.ID + " = " + CiudadArticuloDO.ARTICULO_ID);
        strbuf.append(" WHERE " + CiudadArticuloDO.CIUDAD_ID + " = " + id);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        List<IArticuloDO> ret = new ArrayList<IArticuloDO>();

        ArticuloDO articuloDO;
        while (rs.next()) {
            articuloDO = new ArticuloDO();

            resulSetArticuloDO(articuloDO, rs);
            ret.add(articuloDO);
        }

        return ret;
    }

    @Override
    public byte[] loadImageByArticuloId(int articuloId) throws SQLException {
        StringBuffer strbuf = new StringBuffer();

        strbuf.append("SELECT ");
        strbuf.append(ArticuloDO.IMAGEN);
        strbuf.append(" FROM ");
        strbuf.append(getTableName());
        strbuf.append(" WHERE ");
        strbuf.append(ArticuloDO.ID);
        strbuf.append(" = ");
        strbuf.append(articuloId);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        if (rs.next()) {
            return rs.getBytes(ArticuloDO.IMAGEN);
        }

        return null;
    }

}
